package com.buzzfactory.persistence
{

    import com.buzzfactory.logger.AirLogger;
    
    import flash.data.SQLConnection;
    import flash.data.SQLMode;
    import flash.data.SQLStatement;
    import flash.errors.SQLError;
    import flash.filesystem.File;
    
 


    /**
     * Class demonstrating the synchronous API for working with
     * SQLConnection and SQLStatement instances. The class
     * makes no attempt at error recovery and only logs
     * errors that occur.
     */
	public class TestDB
	{

		public static const TRANSACTION_QUERY:String = 
			"SELECT id, account, category, payee, withdrawal, deposit, date FROM transactions WHERE id = :id";
		public static const TRANSACTION_FIND_ALL:String = 
			"SELECT id, account, category, payee, withdrawal, deposit, date FROM transactions order by date asc";
		public static const TRANSACTION_FIND_BY_ACCOUNT:String = 
			"SELECT  date, payee, (select name from categories c where c.id = t.category) as category,  withdrawal, deposit FROM transactions t where account = :account order by date asc";
		public static const TRANSACTION_INSERT:String = 
			"INSERT INTO transactions (id, account, category, payee, withdrawal, deposit, date) values (:id, :account, :category, :payee, :withdrawal, :deposit, :date)";
		public static const TRANSACTION_DELETE:String =	"DELETE FROM transactions";
		public static const ACCOUNT_FIND_ALL:String = 
			"SELECT id, name FROM accounts order by name";
		public static const ACCOUNT_BALANCE:String = 
			"﻿select (select a.id from accounts a where a.id = t.account) as id, (select a.name from accounts a where a.id = t.account) as account, sum(deposit) - sum(withdrawal) as balance from transactions t group by account";

        [Bindable]
        public var connected:Boolean;        
        private var _sqlConnection:SQLConnection;
        
        /**
         * Establish a connection to the database specified. 
         * @param databaseFile Database file location.
         */
        public function connect(databaseFile:File):void
        {
            try
            {
                _sqlConnection = new SQLConnection();
                _sqlConnection.open(databaseFile, SQLMode.UPDATE);
                // Unless an error was thrown we know we are connected
                // at this point.
                connected = true;
                AirLogger.log("connected");
            }
            catch(sqlError:SQLError)
            {
                AirLogger.log(sqlError.toString());
            }
        }
        
        /**
         * Load an individual transaction and pass the data
         * to the handler.
         * @param id of Transaction to load.
         * @return Array of transaction loaded.
         */
        public function findTransaction(id:int):Array
        {
            try
            {
                var sqlStatement:SQLStatement = new SQLStatement();
                sqlStatement.sqlConnection = _sqlConnection;
                sqlStatement.text = TRANSACTION_QUERY;
                sqlStatement.parameters[":id"] = id;
                sqlStatement.execute();
                AirLogger.log("Transaction with id "+id+" loaded");
    
                // A simplification is made to handle 0 products
                // loaded by returning an Array instead of testing
                // for length 0 and returning null or the Object loaded.
                return sqlStatement.getResult().data;
            }
            catch(sqlError:SQLError)
            {
                AirLogger.log(sqlError.toString());
            }
            return new Array();            
        }


       public function findAllAccounts():Array
        {
            try
            {
                var sqlStatement:SQLStatement = new SQLStatement();
                sqlStatement.sqlConnection = _sqlConnection;
                sqlStatement.text = ACCOUNT_BALANCE;
                sqlStatement.execute();
                AirLogger.log("Accounts loaded");
                return sqlStatement.getResult().data;
            }
            catch(sqlError:SQLError)
            {
                AirLogger.log(sqlError.toString());
            }
            return new Array();            
        }


        
        /**
         * Load a number of random products from the database. All loaded products
         * will be combined into a single Array and passed to the handler function. 
         * @param productCount Number of products to load.
         * @param maxProductID Maximum product ID in the database.
         * @return Array of products loaded.
         */
        public function findTransactions(account:int):Array
        {
            try
            {
                var results:Array = new Array();
                
                var sqlStatement:SQLStatement = new SQLStatement();
                sqlStatement.sqlConnection = _sqlConnection;
                sqlStatement.text = TRANSACTION_FIND_BY_ACCOUNT;
                sqlStatement.parameters[":account"] = account;
                sqlStatement.execute();
                results = sqlStatement.getResult().data;
                var balance:Number = 0;
                for (var i:int = 0; i < results.length; i++) 
                {
                	balance = balance + results[i].deposit - results[i].withdrawal;
                	results[i].balance = balance;
                }
                return results;
            }
            catch(sqlError:SQLError)
            {
                AirLogger.log(sqlError.toString());
            }
            return new Array();
        }
        
         
        public function deleteAllTransactions():Array
        {
            try
            {
                var results:Array = new Array();
                
                var sqlStatement:SQLStatement = new SQLStatement();
                sqlStatement.sqlConnection = _sqlConnection;
                sqlStatement.text = TRANSACTION_DELETE;
                
                sqlStatement.execute();
                results = sqlStatement.getResult().data;
                return results;
            }
            catch(sqlError:SQLError)
            {
                AirLogger.log(sqlError.toString());
            }
            return new Array();
        }


        public function fillDB():Boolean
        {
            try
            {
                
                var sqlStatement:SQLStatement = new SQLStatement();
                var account:int;
                var year:int = 2007;
                var month:int;
                var day:int;
                sqlStatement.sqlConnection = _sqlConnection;
                sqlStatement.text = TRANSACTION_INSERT;
                
                // The same SQLStatement can be reused in the loop
                // since when the execute() call returns and the
                // result extracted it is no longer in use.
                for (var i:int = 0; i < 20000; i++)
                {
                	account = int(Math.random() * 3) + 1;
                    sqlStatement.parameters[":id"] = i;
                    sqlStatement.parameters[":category"] = int(Math.random() * 8) + 1;
                    sqlStatement.parameters[":account"] = account; 
                    sqlStatement.parameters[":payee"] = "sabates";
                    if (int(Math.random() * 100) > 85)
                    {
	                    sqlStatement.parameters[":withdrawal"] = null;
	                    sqlStatement.parameters[":deposit"] = 1200;
                    } else {
	                    sqlStatement.parameters[":withdrawal"] = int(Math.random() * 150) + 1;
	                    sqlStatement.parameters[":deposit"] = null;
                    	
                    }
                    day = int(Math.random()*29)+1;
                    month = int(Math.random()*12)+1;
                    sqlStatement.parameters[":date"] = new Date(year,month,day);
                    sqlStatement.execute();
                    AirLogger.log("TX "+i+" loaded");
                }
                return true;
            }
            catch(sqlError:SQLError)
            {
                AirLogger.log(sqlError.toString());
            }
            return false;
        }



                 
        /**
         * Disconnect from the database. 
         */
        public function disconnect():void
        {
            try
            {
                _sqlConnection.close();
                _sqlConnection = null;
                connected = false;
                AirLogger.log("disconnected");
            }
            catch(sqlError:SQLError)
            {
                AirLogger.log(sqlError.toString());
            }
        }
    }
}